package com.hxx;

import com.mysql.cj.jdbc.Driver;
import org.junit.Test;

import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Properties;

public class TestDemo {

    /**
     * 把链接数据库直接写到类中
     *
     * @throws Exception
     */
    @Test
    public void testConnection() throws Exception {
        //创建驱动对象
        Driver driver = new Driver();
        //注册驱动
        DriverManager.registerDriver(driver);
        //获取连接
        java.sql.Connection connection =
                DriverManager.getConnection("jdbc:mysql://localhost:3306/hxx",
                        "root",
                        "root");
        System.out.println("连接成功");
    }

    /**
     * 从配置文件中读取数据库连接信息
     */
    @Test
    public void testConnection2() throws Exception {
        //加载配置文件
        InputStream inputStream =
                TestDemo.class.getClassLoader()
                        .getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(inputStream);
        //获取配置文件中的数据库连接信息
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        //创建驱动对象，注册驱动
        Class.forName(driver);
        //获取连接
        java.sql.Connection connection =
                DriverManager.getConnection(url,
                        user,
                        password);
        System.out.println("连接成功");
        //--------------创建可以发送sql的对象-------------------
        java.sql.Statement statement = connection.createStatement();
        String sql = "select * from t_user";
        //--------------执行sql语句-返回结果集------------------
        java.sql.ResultSet resultSet = statement.executeQuery(sql);
        //--------------处理结果集-------------------
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("username");
            int age = resultSet.getInt("age");
            Object sex = resultSet.getObject("sex");
            System.out.println(id + "\t" + name + "\t" + age + "\t" + sex);
        }
    }

    /**
     * 防止sql注入
     * 1.使用PreparedStatement对象发送sql语句
     * 2.在sql语句中使用占位符?
     * 3.调用PreparedStatement对象的setXxx方法设置占位符的值
     */
    @Test
    public void testPreparedStatement() throws Exception {
        //加载配置文件
        InputStream inputStream =
                TestDemo.class.getClassLoader()
                        .getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(inputStream);
        //获取配置文件中的数据库连接信息
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        //创建驱动对象，注册驱动
        Class.forName(driver);
        //获取连接
        java.sql.Connection connection =
                DriverManager.getConnection(url,
                        user,
                        password);
        System.out.println("连接成功");
        //--------------创建可以发送sql的对象-------------------
        String sql = "select * from t_user ";
        java.sql.PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //--------------执行sql语句-返回结果集------------------
        java.sql.ResultSet resultSet = preparedStatement.executeQuery();
        //--------------处理结果集-------------------
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("username");
            int age = resultSet.getInt("age");
            Object sex = resultSet.getObject("sex");
            System.out.println(id + "\t" + name + "\t" + age + "\t" + sex);
        }
    }

        /**
         * 登录方法
         */
        @Test
        public void testLogin() throws Exception {
            //login("' or 1=1 -- ", "男");
            login1("' or 1=1 -- ", "男");
        }

        /**
         * 登录方法
         */
        public boolean login(String username, String sex) throws Exception {
            //加载配置文件
            InputStream inputStream =
                    TestDemo.class.getClassLoader()
                            .getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(inputStream);
        //获取配置文件中的数据库连接信息
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        //创建驱动对象，注册驱动
        Class.forName(driver);
        //获取连接
        java.sql.Connection connection =
                DriverManager.getConnection(url,
                        user,
                        password);
        System.out.println("连接成功");
        //--------------创建可以发送sql的对象-------------------
        StringBuilder sb = new StringBuilder();
        sb.append("select count(*) from t_user where username = ");
        sb.append("'").append(username).append("' and sex = '").append(sex).append("'");
        //查询
        java.sql.Statement statement = connection.createStatement();
        //--------------执行sql语句-返回结果集------------------
        java.sql.ResultSet resultSet = statement.executeQuery(sb.toString());
        //--------------处理结果集-------------------
        if (resultSet.next()) {
            System.out.println(resultSet.getInt(1));
        } else {
            System.out.println("登录失败");
        }

        return false;
    }
    /**
     * 防止sql注入
     */
    public boolean login1(String username, String sex) throws Exception {
        //加载配置文件
        InputStream inputStream =
                TestDemo.class.getClassLoader()
                        .getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(inputStream);
        //获取配置文件中的数据库连接信息
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        //创建驱动对象，注册驱动
        Class.forName(driver);
        //获取连接
        java.sql.Connection connection =
                DriverManager.getConnection(url,
                        user,
                        password);
        //--------------创建可以发送sql的对象-------------------
        StringBuilder sb = new StringBuilder();
        sb.append("select count(*) from t_user where username = ? and sex = ?");
        //查询
        java.sql.PreparedStatement preparedStatement =
                connection.prepareStatement(sb.toString());
        //--------------设置占位符的值------------------
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, sex);
        //--------------执行sql语句-返回结果集------------------
        java.sql.ResultSet resultSet = preparedStatement.executeQuery();
        //--------------处理结果集-------------------
        if (resultSet.next()) {
            int anInt = resultSet.getInt(1);
            if (anInt > 0) {
                System.out.println("登录成功");
            } else {
                System.out.println("登录失败");
            }
        } else {
            System.out.println("登录失败");
        }
        //--------------关闭连接------------------
        connection.close();

        return false;
    }
}




















